package cn.lesper.utils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//数据操作层
public class DatabaseUtils extends ExtendBase {

    Connection connection = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    /**
     * @param sql
     * @param params
     * @return true&false
     */
    public boolean excuteUpdate(String sql, List<Object> params) {
        int res = 0;
        try {
            connection = getConnection();
            pstmt = connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
            }
            res = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, connection);
        }
        return res > 0 ? true : false;
    }

    /**
     *
     * @param sql
     * @param params
     * @param clazz
     * @param <T>
     * @return data<T>
     * @throws NoSuchFieldException
     * @throws SecurityException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public <T> List<T> executeQuery(String sql, List<Object> params, Class<T> clazz)
            throws NoSuchFieldException, SecurityException, InstantiationException, IllegalAccessException {
        List<T> data = new ArrayList<T>();
        try {
            connection = getConnection();
            pstmt = connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
            }
            rs = pstmt.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();
            addInstance(clazz, rs, data, rsd);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, connection);
        }
        return data;
    }


    /**
     * @param sql
     * @param params
     * @return List<Object>
     * @throws SecurityException
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    public List<Object> executeQuery(String sql, List<Object> params)
            throws NoSuchFieldException, SecurityException, InstantiationException, IllegalAccessException {
        List<Object> data = new ArrayList<Object>();
        try {
            connection = getConnection();
            pstmt = connection.prepareStatement(sql);

            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
            }
            rs = pstmt.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();
            int columnCount = rsd.getColumnCount();
            addData(rs, data, rsd, columnCount);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, connection);
        }
        return data;
    }


    /**
     * @param sql
     * @param params
     * @return true or false
     */
    public boolean excuteUpdate(String sql, Map<String, Object> params) {
        int res = 0;
        try {
            connection = getConnection();
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                sql = sql.replace(":" + entry.getKey(), (CharSequence) entry.getValue());
            }
            pstmt = connection.prepareStatement(sql);
            res = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, connection);
        }
        return res > 0 ? true : false;
    }

    /**
     * @param sql
     * @param params
     * @return List<T>
     * @throws SecurityException
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    public <T> List<T> executeQuery(String sql, Map<String, Object> params, Class<T> clazz)
            throws NoSuchFieldException, SecurityException, InstantiationException, IllegalAccessException {
        List<T> data = new ArrayList<T>();
        try {
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                sql = sql.replace(":" + entry.getKey(), (CharSequence) entry.getValue());
            }
            connection = getConnection();
            pstmt = connection.prepareStatement(sql);
            rs = pstmt.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();
            addInstance(clazz, rs, data, rsd);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return data;
    }

    /**
     * @param sql
     * @param params
     * @return List<Object>
     * @throws SecurityException
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    public List<Object> executeQuery(String sql, Map<String, Object> params)
            throws NoSuchFieldException, SecurityException, InstantiationException, IllegalAccessException {
        List<Object> data = new ArrayList<Object>();
        try {
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                sql = sql.replace(":" + entry.getKey(), (CharSequence) entry.getValue());
            }
            connection = getConnection();
            pstmt = connection.prepareStatement(sql);

            rs = pstmt.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();
            int columnCount = rsd.getColumnCount();
            addData(rs, data, rsd, columnCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return data;
    }

    /**
     * @note 对象重构添加到实例
     * @param clazz
     * @param rs
     * @param data
     * @param rsd
     * @param <T>
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws NoSuchFieldException
     */
    private <T> void addInstance(Class<T> clazz, ResultSet rs, List<T> data, ResultSetMetaData rsd) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException {
        while (rs.next()) {
            T t = clazz.newInstance();
            for (int i = 0; i < rsd.getColumnCount(); i++) {
                String col_name = rsd.getColumnName(i + 1);
                Object value = rs.getObject(col_name);
                Field field = clazz.getDeclaredField(col_name);
                field.setAccessible(true);
                field.set(t, value);
            }
            data.add(t);
        }
    }


    /**
     * @note 对象重构添加到Map<String,Object>
     * @param rs
     * @param data
     * @param rsd
     * @param columnCount
     * @throws SQLException
     */
    private void addData(ResultSet rs, List<Object> data, ResultSetMetaData rsd, int columnCount) throws SQLException {
        while (rs.next()) {
            Map<String, Object> rowData = new HashMap<String, Object>();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(rsd.getColumnName(i), rs.getObject(i));
            }
            data.add(rowData);
        }
    }

}

